﻿<?php	

			 
			 echo '<table id="rank" cellspacing="0" cellpadding="0" class="roundRobin" width="900">
					   <thead>
						  <tr>
							 <th class="first" abbr="\Nr&quot;" scope="col">Nr</th>
							 <th scope="col">Team / WG</th>
							 <th scope="col">+/-/Unent.</th>
							 <th scope="col">Punkte</th>
							 <th scope="col">Zusatzpunkte</th>
							 <th class="last" scope="col">Gesamt</th>
						  </tr>
					   </thead>
					   <tfoot>
						  <tr>
							 <td class="first"></td>
							 <td colspan="2"></td>
							 <td colspan="2"></td>
							 <td class="last"></td>
						  </tr>
					   </tfoot><tbody>';
			 
					 
	$abfrage = "SELECT t.name, (SELECT SUM(CASE WHEN xp.team_id=t.name THEN xp.points ELSE 0 END) FROM x_points AS xp) AS xpp,
		SUM(CASE WHEN (rr.heimteam=t.name AND rr.heimpoints=3) OR (rr.gastteam=t.name AND rr.gastpoints=3) THEN 1 ELSE 0 END) as siege, 
		SUM(CASE WHEN (rr.heimteam=t.name AND rr.heimpoints=1) OR (rr.gastteam=t.name AND rr.gastpoints=1) THEN 1 ELSE 0 END) as unentschieden, 
		SUM(CASE WHEN (rr.heimteam=t.name AND rr.heimpoints=0) OR (rr.gastteam=t.name AND rr.gastpoints=0) THEN 1 ELSE 0 END) as niederlagen, 
		/*SUM(CASE WHEN rr.heimteam=t.name THEN rr.heimpoints ELSE 0 END) as hp, SUM(CASE WHEN rr.gastteam=t.name THEN rr.gastpoints ELSE 0 END) as gp, */
		CAST( 
			SUM(CASE WHEN rr.gastteam=t.name THEN rr.gastpoints ELSE 0 END)
			AS SIGNED INT ) + CAST(    
			  SUM(CASE WHEN rr.heimteam=t.name THEN rr.heimpoints ELSE 0 END)
			 AS SIGNED INT 
		) as rr_gesamt, 
		CAST( 
			(SELECT SUM(CASE WHEN xp.team_id=t.name THEN xp.points ELSE 0 END) FROM x_points AS xp)
			AS SIGNED INT ) + CAST( 
			SUM(CASE WHEN rr.gastteam=t.name THEN rr.gastpoints ELSE 0 END)
			AS SIGNED INT ) + CAST(    
			  SUM(CASE WHEN rr.heimteam=t.name THEN rr.heimpoints ELSE 0 END)
			 AS SIGNED INT 
		) AS gesamt_points
		FROM teams AS t , rr_games AS rr
		GROUP BY t.name ORDER BY gesamt_points DESC";
						 			 		
			 
			$ergebnis = mysql_query($abfrage)
				OR die("Error: $abfrage <br>".mysql_error());
				
				$cnt = 1;
			while($row = mysql_fetch_object($ergebnis))
			   {				 
					echo '
					  <tr class="rowA">
						 <td class="first">'.$cnt.'</td>
						 <td>'.$row->name.'</td>
						 <td><input type="text" value='.$row->siege.' readonly> : <input  type="text" value='.$row->niederlagen.' readonly> : <input  type="text" value='.$row->unentschieden.' readonly></td>
						 						 <td><input type="text" value='.$row->rr_gesamt.' readonly></td>
												 <td><input type="text"value='.$row->xpp.' readonly></td>
						 <td class="last"><input type="text" value='.$row->gesamt_points.' readonly></td>
					  </tr>';
					$cnt++;
			   }
			   echo '</tbody></table><br/>';
			   
		 ?>